package com.travel.manager.util;

import com.travel.common.pojo.City;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelTest {

    public static void main(String[] args) throws Exception {
        testRead(new File("f:/city.xls"));
//        List<City> cityList = new ArrayList<>();
//        City c1 = new City();
//        c1.setId(1);
//        c1.setName("北京");
//        City c2 = new City();
//        c2.setId(2);
//        c2.setName("上海");
//        cityList.add(c1);
//        cityList.add(c2);
//        writeExcelXLSX(cityList);
    }

    public static File exportXls(List<City> cityList, String fileName) {
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("学生表一");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("城市id");
        row.createCell(1).setCellValue("名称");
        int rowCount = 1;
        for (City city : cityList) {
            HSSFRow tmp = sheet.createRow(rowCount);
            tmp.createCell(0).setCellValue(city.getId());
            tmp.createCell(1).setCellValue(city.getName());
            rowCount++;
        }
        File excel = new File(fileName);
        try {
            FileOutputStream fout = new FileOutputStream(excel);
            wb.write(fout);
            wb.close();
            fout.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return excel;
    }

    public static void writeExcelXLSX(List<City> cityList) {
        // 第一步，创建一个webbook，对应一个Excel文件
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("学生表一");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        SXSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("城市id");
        row.createCell(1).setCellValue("名称");
        int rowCount = 1;
        for (City city : cityList) {
            SXSSFRow tmp = sheet.createRow(rowCount);
            tmp.createCell(0).setCellValue(city.getId());
            tmp.createCell(1).setCellValue(city.getName());
            rowCount++;
        }
        try {
            FileOutputStream fout = new FileOutputStream("F:/city.xlsx");
            workbook.write(fout);
            workbook.close();
            fout.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void testRead(File file) throws Exception {
        FileInputStream is = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(is);
        int sheetCount = workbook.getNumberOfSheets();  //Sheet的数量
        //这里可以遍历每个sheet略
        Sheet sheet = workbook.getSheetAt(0);//第一个sheet
        int rowCount = sheet.getPhysicalNumberOfRows();//总行数
        for (int i = 0; i < rowCount; i++) {
            Row row = sheet.getRow(i);//读取第i行
            int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
            for (int j = 0; j < cellCount; j++) {
                Cell cell = row.getCell(j);
                CellType cellType = cell.getCellTypeEnum();
                switch (cellType) {
                    case STRING: //文本
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                    case NUMERIC: //数字、日期
                        if (DateUtil.isCellDateFormatted(cell)) {
                            SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
                            //fmt.format(cell.getDateCellValue()); //日期型
                        } else {
                            System.out.print(cell.getNumericCellValue() + "\t");
                        }
                        break;

                }

            }
            System.out.println();
        }
        workbook.close();
        is.close();

    }

    public static List<City> readCity(MultipartFile file) throws Exception {
        InputStream is = file.getInputStream();
        Workbook workbook = WorkbookFactory.create(is);
        int sheetCount = workbook.getNumberOfSheets();  //Sheet的数量
        //这里可以遍历每个sheet略
        Sheet sheet = workbook.getSheetAt(0);//第一个sheet
        int rowCount = sheet.getPhysicalNumberOfRows();//总行数
        List<City> cityList = new ArrayList<>();
        for (int i = 0; i < rowCount; i++) {
            Row row = sheet.getRow(i);//读取第i行
            int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
            for (int j = 0; j < cellCount; j++) {
                Cell cell = row.getCell(j);
                City temp = new City();
                temp.setName(cell.getStringCellValue());
                cityList.add(temp);
            }
        }
        workbook.close();
        is.close();
        return cityList;
    }
}